import traceback
import pyautogui
import os
import time
import pyperclip as clip
import json
import pandas as pd
from datetime import datetime
from sqlalchemy import create_engine
import pypinyin
from bs4 import BeautifulSoup
import random
from jsjj.util.configUtil import *
from jsjj.util.dbUtil import *
from jsjj.util.emailUtil import *
from jsjj.util.logUtil import *
from jsjj.util.timeUtil import *
from jsjj.util.mouseMoveUtil import *

# 我们内部的处理，实际上是处理的列表，也就是实际返回的数据，固定返回的一个月的数据，那么默认就是月模式
runMode = '周模式'
# 拼多多-商家后台-数据中心-交易数据
modeName = '拼多多_商家后台_交易数据'
# 月模式
selectMonth = {'1月': [1199, 429], '2月': [1274, 330], '3月': [1352, 330],
               '4月': [1199, 365], '5月': [1274, 365], '6月': [1352, 365],
               '7月': [1199, 403], '8月': [1274, 403], '9月': [1352, 403],
               '10月': [1199, 440], '11月': [1274, 440], '12月': [1352, 440]}

# 周模式
# '1': [1226, 352]  第一行
selectWeek = {'1': [1199, 430], '2': [1199, 460], '3': [1199, 490],
              '4': [1199, 520], '5': [1199, 550]}


# 左边点击，直到点击到一级类目，该过程不参与循环
def SPGK(engine, conn):
    # 关闭电霸每天晚上会弹出的一个屏蔽页面
    pyautogui.FAILSAFE = False

    # 左边--【物流工具】
    sleep(6)
    pyautogui.scroll(5000)
    # 左边--【物流工具】
    viaCommonUrl('https://mms.pinduoduo.com/orders/order/carriage/list')

    sleep(4)
    pyautogui.keyDown('f12')
    pyautogui.keyUp('f12')

    # 左边--【交易数据】
    # ************修改为url方式，菜单位置总是变化************************************************************
    viaCommonUrl('https://mms.pinduoduo.com/sycm/stores_data/operation')

    jsonFromWebShopName = getSjhtShopName()

    # 日模式/周模式/月模式
    pyautogui.moveTo(1313, 799, 4)
    pyautogui.scroll(-2000)

    if runMode == '周模式':
        pyautogui.moveTo(1317, 328, 4)
        pyautogui.click()

        currentWeekInThisMonth = getWeekOfMonth(datetime.datetime.now().year, datetime.datetime.now().month,
                                                datetime.datetime.now().day)

        # 动态计算出来，默认上周的数据
        currentWeekInThisMonth = currentWeekInThisMonth - 1
        pyautogui.moveTo(selectWeek[str(currentWeekInThisMonth)][0], selectWeek[str(currentWeekInThisMonth)][1], 4)
        pyautogui.click()
        sleep(5)

    elif runMode == '日模式':
        pass
    elif runMode == '月模式':
        # .....
        pyautogui.moveTo(selectMonth[str(datetime.datetime.now().month - 1) + "月"][0],
                         selectMonth[str(datetime.datetime.now().month - 1) + "月"][1], 2)
        pyautogui.click()
        sleep(5)

    # ==============F12的filter过滤框(web1只是数组中的一个元素而已，因此直接处理list就好了)
    # pyautogui.moveTo(1571, 167, 6)
    # pyautogui.doubleClick()
    #
    # # 过滤  jsonFromWeb1
    # clip.copy('queryMallDataPageOverView')  # 先复制
    # pyautogui.hotkey('ctrl', 'v')  # 再粘贴
    #
    # # 选中过滤文件
    # pyautogui.moveTo(1540, 325, 6)
    # pyautogui.click()
    #
    # # 点中json值区域
    # pyautogui.moveTo(1812, 497, 6)
    # pyautogui.click()
    #
    # sleep(2)
    # pyautogui.hotkey('ctrl', 'a')
    #
    # sleep(2)
    # pyautogui.hotkey('ctrl', 'c')
    #
    # # 读取剪切板内容
    # sleep(2)
    # value = clip.paste()
    # jsonFromWeb1 = json.loads(value)

    # ==============F12的filter过滤框
    pyautogui.moveTo(1571, 167, 6)
    pyautogui.doubleClick()

    # 过滤  jsonFromWeb2
    clip.copy('queryMallDataPageOverViewList')  # 先复制
    pyautogui.hotkey('ctrl', 'v')  # 再粘贴

    # 选中过滤文件（第二个文件才是对的），我们没有取第一个了，所以由第二个，又变成了第一个
    pyautogui.moveTo(1540, 325, 6)
    pyautogui.click()

    # 点中json值区域
    pyautogui.moveTo(1812, 497, 6)
    pyautogui.click()

    sleep(2)
    pyautogui.hotkey('ctrl', 'a')

    sleep(2)
    pyautogui.hotkey('ctrl', 'c')

    # 读取剪切板内容
    sleep(2)
    value = clip.paste()
    jsonFromWeb2 = json.loads(value)

    # jsonDf = {'cfmOrdrAmt': [jsonFromWeb1['result']['cfmOrdrAmt']],
    #           'cfmOrdrAmtPct': [jsonFromWeb1['result']['cfmOrdrAmtPct']],
    #           'cfmOrdrAmtIsPercent': [jsonFromWeb1['result']['cfmOrdrAmtIsPercent']],
    #           'cfmOrdrUsrCnt': [jsonFromWeb1['result']['cfmOrdrUsrCnt']],
    #           'cfmOrdrUsrCntPct': [jsonFromWeb1['result']['cfmOrdrUsrCntPct']],
    #           'cfmOrdrUsrCntIsPercent': [jsonFromWeb1['result']['cfmOrdrUsrCntIsPercent']],
    #           'payOrdrAmt': [jsonFromWeb1['result']['payOrdrAmt']],
    #           'payOrdrAmtPct': [jsonFromWeb1['result']['payOrdrAmtPct']],
    #           'payOrdrAmtIsPercent': [jsonFromWeb1['result']['payOrdrAmtIsPercent']],
    #           'payOrdrUsrCnt': [jsonFromWeb1['result']['payOrdrUsrCnt']],
    #           'payOrdrUsrCntPct': [jsonFromWeb1['result']['payOrdrUsrCntPct']],
    #           'payOrdrUsrCntIsPercent': [jsonFromWeb1['result']['payOrdrUsrCntIsPercent']],
    #           'cfmOrdrCnt': [jsonFromWeb1['result']['cfmOrdrCnt']],
    #           'cfmOrdrCntPct': [jsonFromWeb1['result']['cfmOrdrCntPct']],
    #           'cfmOrdrCntIsPercent': [jsonFromWeb1['result']['cfmOrdrCntIsPercent']],
    #           'payOrdrCnt': [jsonFromWeb1['result']['payOrdrCnt']],
    #           'payOrdrCntPct': [jsonFromWeb1['result']['payOrdrCntPct']],
    #           'payOrdrCntIsPercent': [jsonFromWeb1['result']['payOrdrCntIsPercent']],
    #           'payOrdrAup': [jsonFromWeb1['result']['payOrdrAup']],
    #           'payOrdrAupPct': [jsonFromWeb1['result']['payOrdrAupPct']],
    #           'payOrdrAupIsPercent': [jsonFromWeb1['result']['payOrdrAupIsPercent']],
    #           'payUvRto': [jsonFromWeb1['result']['payUvRto']],
    #           'payUvRtoPct': [jsonFromWeb1['result']['payUvRtoPct']],
    #           'payUvRtoIsPercent': [jsonFromWeb1['result']['payUvRtoIsPercent']],
    #           'mallFavCnt': [jsonFromWeb1['result']['mallFavCnt']],
    #           'mallFavCntPct': [jsonFromWeb1['result']['mallFavCntPct']],
    #           'mallFavCntIsPercent': [jsonFromWeb1['result']['mallFavCntIsPercent']],
    #           'rpayOrdrUsrRto': [jsonFromWeb1['result']['rpayOrdrUsrRto']],
    #           'rpayOrdrUsrRtoPct': [jsonFromWeb1['result']['rpayOrdrUsrRtoPct']],
    #           'rpayOrdrUsrRtoIsPercent': [jsonFromWeb1['result']['rpayOrdrUsrRtoIsPercent']],
    #           'cfmOrdrAmt1dPln': [jsonFromWeb2['result'][0]['cfmOrdrAmt1dPln']],
    #           'cfmOrdrAmt1dOst': [jsonFromWeb2['result'][0]['cfmOrdrAmt1dOst']],
    #           'cfmOrdrUsrCnt1dPln': [jsonFromWeb2['result'][0]['cfmOrdrUsrCnt1dPln']],
    #           'cfmOrdrUsrCnt1dOst': [jsonFromWeb2['result'][0]['cfmOrdrUsrCnt1dOst']],
    #           'payOrdrAmt1dPln': [jsonFromWeb2['result'][0]['payOrdrAmt1dPln']],
    #           'payOrdrAmt1dOst': [jsonFromWeb2['result'][0]['payOrdrAmt1dOst']],
    #           'payOrdrUsrCnt1dPln': [jsonFromWeb2['result'][0]['payOrdrUsrCnt1dPln']],
    #           'payOrdrUsrCnt1dOst': [jsonFromWeb2['result'][0]['payOrdrUsrCnt1dOst']],
    #           'cfmOrdrCnt1dPln': [jsonFromWeb2['result'][0]['cfmOrdrCnt1dPln']],
    #           'cfmOrdrCnt1dOst': [jsonFromWeb2['result'][0]['cfmOrdrCnt1dOst']],
    #           'payOrdrCnt1dPln': [jsonFromWeb2['result'][0]['payOrdrCnt1dPln']],
    #           'payOrdrCnt1dOst': [jsonFromWeb2['result'][0]['payOrdrCnt1dOst']],
    #           'payOrdrAup1dPln': [jsonFromWeb2['result'][0]['payOrdrAup1dPln']],
    #           'payOrdrAup1dOst': [jsonFromWeb2['result'][0]['payOrdrAup1dOst']],
    #           'payUvRto1dPln': [jsonFromWeb2['result'][0]['payUvRto1dPln']],
    #           'payUvRto1dOst': [jsonFromWeb2['result'][0]['payUvRto1dOst']],
    #           'mallFavCnt1dOst': [jsonFromWeb2['result'][0]['mallFavCnt1dOst']],
    #           'mallFavCnt1dPln': [jsonFromWeb2['result'][0]['mallFavCnt1dPln']],
    #           'rpayOrdrUsrRto1dPln': [jsonFromWeb2['result'][0]['rpayOrdrUsrRto1dPln']],
    #           'rpayOrdrUsrRto1dOst': [jsonFromWeb2['result'][0]['rpayOrdrUsrRto1dOst']]
    #           }
    df = pd.read_json(json.dumps(jsonFromWeb2['result']))
    # df['statDate'] = datetime.datetime.now() 从上面的json中获取
    df['shopName'] = jsonFromWebShopName['result']['username']

    df['运行模式'] = runMode
    df['统计月'] = ''
    df['统计周'] = ''
    df['统计日'] = ''
    df['统计日周月'] = ''
    df['插入时间'] = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    if runMode == '月模式':
        df['统计月'] = str(datetime.datetime.now().month - 1) + "月"
        df['统计日周月'] = df['统计月']
    if runMode == '周模式':
        df['统计周'] = getLastWeekOfYear(datetime.datetime.now().year, datetime.datetime.now().month,
                                      datetime.datetime.now().day)
        df['统计日周月'] = df['统计周']
    if runMode == '日模式':
        df['统计日'] = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime(
            "%Y-%m-%d %H:%M:%S")  # 减一天，统计的是昨天的数据
        df['统计日周月'] = df['统计日']
    # 解决可能出现的超时问题bugfix 2020.08.18
    conn.connection.connection.ping(reconnect=True)
    # 拼多多_商家后台_交易数据
    df.to_sql(name='拼多多_商家后台_交易数据', con=conn, if_exists='append', index=False)

    resetGuiAtLastStepCloseF12ToTop()

    return


def deleteDuplicationData(engine):
    # 这里有特殊的逻辑，不同账号，可能有相同的类目，同一天的数据，是一模一样的，因此我们需要去除重复数据的
    # 拼多多_商家后台_交易数据
    sql = 'delete from a                                                              '
    sql += '    using 拼多多_商家后台_交易数据 as a, 拼多多_商家后台_交易数据 as b   '
    sql += '    where (a.id < b.id)                                                    '
    sql += '    and (a.statDate = b.statDate and a.运行模式=b.运行模式 and a.shopName=b.shopName )     '
    engine.execute(sql)


def executeCatchWeb(engine, conn):
    try:
        LogTaskAndMachine('拼多多_商家后台_交易数据', engine, conn, '', runMode)
        SPGK(engine, conn)
        deleteDuplicationData(engine)
    except Exception as e:
        traceback.print_exc()
        sendAlert('【异常中断】' + modeName, '异常:' + str(e) + '|报错文件:' + os.path.split(__file__)[-1], engine, conn)
        print('【异常中断】' + modeName, '异常:' + str(e) + '|报错文件:' + os.path.split(__file__)[-1])
        return
    sendFinalSuccessEmail('●正常完成●' + modeName, '', engine, conn, modeName, runMode)


if __name__ == '__main__':
    # 连接database
    engine, conn = getConn()
    executeCatchWeb(engine, conn)
